package com.tianruan.dc.common.utils;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Author: Dong
 * @Email: dong980514280@gmail.com
 * @Create: 2022-03-04 15:39
 * @PS 微软Office工具类
 */
public class MOfficeUtils {

    // 获取工作簿
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        String fileType = "";
        try {
            fileType = FileTypeUtils.getFileType(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            try {
                //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
                if (fileType.equals("xls")) {
                    //2003
                    workbook = new HSSFWorkbook(is);
                } else if (fileType.equals("xlsx")) {
                    //2007 及2007以上
                    workbook = new XSSFWorkbook(is);
                }
            } catch (OfficeXmlFileException e) {
//                if (e.getMessage().contains("The supplied data appears to be in the Office 2007+ XML")) {
//                    workbook = new XSSFWorkbook(is);
//                }
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }catch (IOException e) {
            e.getMessage();
        }
        return workbook;
    }


    public static String getCellValueToString(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        int cellType = cell.getCellType();
        switch(cellType){
            case Cell.CELL_TYPE_NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if(DateUtil.isCellDateFormatted(cell)){
                    SimpleDateFormat sdf = null;
                    if(format ==20|| format ==32){
                        sdf = new SimpleDateFormat("HH:mm");
                    }else if(format ==14|| format ==31|| format ==57|| format ==58){

                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else{
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());
                    }catch(Exception e){
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        }catch(Exception e1){
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }else{
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue ="";
                break;
        }
        return cellValue.trim().replace(" ","");
    }

}
